---EXEC sp_create_update_fab_lot 'LDS273ACG04','LDJ22119.1','Running', 24, '3159', '06/01/2006', 'NSD-Mask-CED', '214/472', '09/30/2006','SILTERRA', NULL, NULL, NULL, NULL

ALTER proc sp_create_update_fab_lot (

@part_number 		varchar(50), 
@lot_number 		varchar(50), 
@lot_status 		varchar(50), 
@wafer_qty_in 		int, 
@po_number 			varchar(50),
@date_in 			datetime,
@lot_stage_name 	varchar(50),
@lot_stage_position varchar(50),
@expected_date_out 	datetime,
@vendor_name 		varchar(50),
@wafer_qty_out 		varchar(50), 
@date_out 			datetime,
@outLook_date 		datetime,
@wafer_Id 			varchar(100),
@ps_type			varchar(50),
@lot_Category		varchar(10)
)
as
begin
	declare @process_step_id 	int
	declare @epiVT_ps_id 		int
	declare @epiSEAL_ps_id 		int
	declare @location_id 		int
	declare @Lawrencelocation_id int
	declare @lot_id 			int
	DECLARE @epiVTlot_id   		INT
	DECLARE @epiSEALlot_id   	INT
	DECLARE @rowCount	   		INT

	if @date_in is null OR LTRIM( RTRIM(@DATE_IN)) = ''
	begin	
		set @date_in = CURRENT_TIMESTAMP
	end
	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end

	select @process_step_id = PS.ProcessStep_ID from product P,Process_Step PS where P.part_number = @part_number and PS_Type_Name = 'FAB' and PS_Type = @ps_type and P.ProcessStep_ID = PS.ProcessStep_ID
	select @location_id = Location_ID from Location where L_Type = 'Fab' and L_Vendor_Name = @vendor_name	
	select @Lawrencelocation_id = Location_ID from Location where L_Type = 'Fab' and L_Vendor_Name = 'Lawrence'
	
	if @process_step_id is null -- new Product or Product Name mismatch
	begin
		select @process_step_id = PS.ProcessStep_ID from Product_Map_Table PMT,Process_Step PS where PMT.part_number = @part_number and PS_Type_Name = 'FAB' and PS_Type = @ps_type and PMT.ProcessStep_ID=PS.ProcessStep_ID
		if @process_step_id is null
		begin
			IF @PROCESS_STEP_ID IS NULL
			BEGIN
				--For Discrepancy Lot Notification Alert
				if ( select count(*) from Notification_Alert_Table where part_number = @part_number and Lot_Number = @lot_number and Fab_Lot_Number = @lot_number and Vendor_Name = @vendor_name and Process_Type = @ps_type and Is_USER_Notified = 1 ) = 0 
				begin
					INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty,Wafer_id, Status,TimeStamp )
						VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @ps_type,'Discrepancy Lot Notification', @wafer_qty_in, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
				end
				
				update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
			END
		END
	END
	
	/*SET @rowCount = 0
	SELECT @rowCount = COUNT(*) FROM Lot_Table WHERE LOT_Number = @lot_number AND Lot_Current_Status <> 'Close' and ProcessStep_Id IN ( SELECT ProcessStep_ID FROM fn_get_process_flows_by_ProcessStep_ID ( @process_step_id, 0 ) WHERE ProcessStep_Id != @process_step_id )
	IF( @rowCount IS NOT NULL AND @rowCount > 0 )
	BEGIN
		Update Lot_Table SET Lot_Current_Status = 'Close' WHERE LOT_Number = @lot_number AND ProcessStep_Id IN ( SELECT ProcessStep_ID FROM fn_get_process_flows_by_ProcessStep_ID ( @process_step_id, 0 ) WHERE ProcessStep_Id != @process_step_id )
	END*/
	
	IF @ps_type = 'VN_EXP'
	BEGIN
		SELECT @epiVT_ps_id = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@part_number,'Fab','Epi VT')
		
		IF @epiVT_ps_id IS NULL
		BEGIN
			--For Discrepancy Lot Notification Alert
			if ( select count(*) from Notification_Alert_Table where part_number = @part_number and Lot_Number = @lot_number and Fab_Lot_Number = @lot_number and Vendor_Name = 'Lawrence' and Process_Type = 'Epi VT' and Is_USER_Notified = 1 ) = 0 
			begin
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty,Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @lot_number, 'Lawrence', 'Epi VT','Discrepancy Lot Notification', @wafer_qty_in, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
			end
			
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
		END

		select @epiVTlot_id = Lot_ID from Lot_Table where ProcessStep_ID = @epiVT_ps_id and Location_ID = @Lawrencelocation_id and Lot_Number = @lot_number

		IF @epiVTlot_id IS NULL
		BEGIN
			IF @epiVT_ps_id IS NOT NULL AND @Lawrencelocation_id IS NOT NULL
			BEGIN
				insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Wafer_Qty_Out, Date_In, Date_Out,Stage_Name, Stage_Position,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Drop_Out, Po_Number, Lot_category) 
					values (@epiVT_ps_id, @Lawrencelocation_id, @lot_number,@lot_number,@lot_number,@wafer_qty_in, @wafer_qty_out,@date_in,@date_out,@lot_stage_name, @lot_stage_position, 'Close', CURRENT_TIMESTAMP, 1 , 0, @po_number, @lot_Category )	
				select @epiVTlot_id = @@IDENTITY
				insert into FAB_Lot(Lot_ID, FL_Expected_Date_Out, FL_OutLook_Date ,Wafer_Id, Timestamp ) values (@epiVTlot_id, @expected_date_out, @outLook_Date, @Wafer_Id, CURRENT_TIMESTAMP )
				INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS, SL_NEXT_PROCESS, SHIP_DROP_OUT, SL_CUSTOMER_PO, SL_NEXTLOCATION, WAFER_ID ) 
					VALUES( @epiVTlot_id, @date_in, @wafer_qty_in, CURRENT_TIMESTAMP, 'Close', 1, @ps_type, 0, @po_number, 'Jazz',   @Wafer_Id )
			END
		END
	END 

	IF @ps_type = 'FAB'
	BEGIN

		SELECT @epiSEAL_ps_id = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@part_number,'Fab','Epi SEAL')
		
		IF @epiSEAL_ps_id IS NULL
		BEGIN
			--For Discrepancy Lot Notification Alert
			if ( select count(*) from Notification_Alert_Table where part_number = @part_number and Lot_Number = @lot_number and Fab_Lot_Number = @lot_number and Vendor_Name = 'Lawrence' and Process_Type = 'Epi SEAL' and Is_USER_Notified = 1 ) = 0 
			begin
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty,Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @lot_number, 'Lawrence', 'Epi SEAL','Discrepancy Lot Notification', @wafer_qty_in, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
			end
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
		END

		select @epiSEALlot_id = Lot_ID from Lot_Table where ProcessStep_ID = @epiSEAL_ps_id and Location_ID = @Lawrencelocation_id and Lot_Number = @lot_number
		IF @epiSEALlot_id IS NULL
		BEGIN
			IF @epiSEAL_ps_id IS NOT NULL AND @Lawrencelocation_id IS NOT NULL
			BEGIN
				insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Wafer_Qty_Out, Date_In, Date_Out,Stage_Name, Stage_Position,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Drop_Out, Po_Number, Lot_category) 
					values (@epiSEAL_ps_id, @Lawrencelocation_id, @lot_number,@lot_number,@lot_number,@wafer_qty_in, @wafer_qty_out,@date_in,@date_out,@lot_stage_name, @lot_stage_position, 'Close', CURRENT_TIMESTAMP, 1 , 0, @po_number, @lot_Category )	
				select @epiSEALlot_id = @@IDENTITY
				insert into FAB_Lot(Lot_ID, FL_Expected_Date_Out, FL_OutLook_Date ,Wafer_Id, Timestamp ) values (@epiSEALlot_id, @expected_date_out, @outLook_Date, @Wafer_Id, CURRENT_TIMESTAMP )
				INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS, SL_NEXT_PROCESS, SHIP_DROP_OUT, SL_CUSTOMER_PO, SL_NEXTLOCATION, WAFER_ID ) 
					VALUES( @epiSEALlot_id, @date_in, @wafer_qty_in, CURRENT_TIMESTAMP, 'Close', 1, @ps_type, 0, @po_number, 'Jazz',   @Wafer_Id )
			END
		END
	END 


	select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	
	if @lot_id is null -- Check if the lot exists
	begin -- Create Lot
		IF @PROCESS_STEP_ID IS NOT NULL AND @LOCATION_ID IS NOT NULL
		BEGIN
			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Wafer_Qty_Out, Date_In, Date_Out,Stage_Name, Stage_Position,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Drop_Out, Po_Number, Lot_category) 
				values (@process_step_id, @location_id, @lot_number,@lot_number,@lot_number,@wafer_qty_in, @wafer_qty_out,@date_in,@date_out,@lot_stage_name, @lot_stage_position, @lot_status, CURRENT_TIMESTAMP, 1 , 0, @po_number, @lot_Category )
			select @lot_id = @@IDENTITY
			
			-- For New Lot Notification Alert
			INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty,Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @ps_type ,'New Lot Notification', @wafer_qty_in, @wafer_Id, 'Open', CURRENT_TIMESTAMP )		
					
			insert into FAB_Lot(Lot_ID, FL_Expected_Date_Out, FL_OutLook_Date ,Wafer_Id, Timestamp ) values (@lot_id, @expected_date_out, @outLook_Date, @Wafer_Id, CURRENT_TIMESTAMP )
		END
		
	end
	else
	begin 
		update Lot_Table set Lot_Current_Status = @lot_status, Wafer_Qty_In = @wafer_qty_in, Stage_Name = @lot_stage_name, Stage_Position = @lot_stage_position, Lot_Drop_Out = 0, PO_Number = @po_number, Lot_Category = @lot_Category where Lot_ID = @lot_id		
		
		update FAB_Lot set FL_Expected_Date_Out = @expected_date_out, FL_OutLook_Date = @outLook_Date, Timestamp=CURRENT_TIMESTAMP, Wafer_Id = @Wafer_Id where Lot_ID = @lot_id	
	end	
END
